package com.wdt.data.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.wdt.data.model.*;
import com.wdt.data.service.*;
import com.wdt.data.util.SpringContextUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PoiUtil {


    private static Map company = new HashMap();
    private static Map<String, String> companyCodeMap = new HashMap();
    private static Map city = new HashMap();
    private static Map<String, Integer> heatSourceMap = new HashMap<>();
    private static Map<String, String> heatSourceCodeMap = new HashMap<>();
    private static Map<String, Integer> heatStationMap = new HashMap<>();
    private static Map<String, String> heatStationCodeMap = new HashMap<>();
    private static Map<String, Integer> communityMap = new HashMap<>();
    private static Map<String, String> communityCodeMap = new HashMap<>();
    private static Map<String, Integer> crewMap = new HashMap<>();
    private static Map<String, String> crewCodeMap = new HashMap<>();
    private static Map<String, Integer> buildMap = new HashMap<>();
    private static Map<String, String> buildCodeMap = new HashMap<>();
    private static Map<String, Integer> heatsourceExitMap = new HashMap<>();

    public void readExcelFileForHeatSource(InputStream inputStream, String fileName) {
        IBasHeatsourceService heatsourceService = SpringContextUtils.getBean(IBasHeatsourceService.class);
        getCompany();
        List<BasHeatsource> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            System.out.println("文件为空");
        } else {
            QueryWrapper<BasHeatsource> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    Map result = new HashMap();
                    row = sheet.getRow(j);
                    BasHeatsource heatsource = new BasHeatsource();
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        result.put("wt1", "对应企业数据为空");
                    }
                    if (company.get(row.getCell(1).getStringCellValue()) == null || "".equals(company.get(row.getCell(1).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应供热企业");
                        result.put("wt1", "无对应供热企业");
                    } else {
                        heatsource.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(1).getStringCellValue()).toString()));
                        result.put("companyName", row.getCell(1).getStringCellValue());
                        queryWrapper = new QueryWrapper<>();
                        queryWrapper.likeRight("code", companyCodeMap.get(row.getCell(1).getStringCellValue())).orderByDesc("code").last("limit 1");
                        BasHeatsource basHeatsource = heatsourceService.getOne(queryWrapper);
                        if (basHeatsource != null) {
                            heatsource.setCode((Long.parseLong(basHeatsource.getCode()) + 1) + "");
                        } else {
                            heatsource.setCode(companyCodeMap.get(row.getCell(1).getStringCellValue()) + "01");
                        }
                    }
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        result.put("wt2", "热源名称为空");
                    } else {
                        heatsource.setHeatSourceName(row.getCell(2).getStringCellValue());
                    }
                    result.put("heatSourceName", row.getCell(2).getStringCellValue());

                    heatsource.setAddress(row.getCell(4).getStringCellValue());

                    result.put("address", row.getCell(4).getStringCellValue());
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setHeatingArea(row.getCell(5).getStringCellValue());

                    result.put("heatArea", row.getCell(5).getStringCellValue());
                    row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setAreaResident(row.getCell(6).getStringCellValue());

                    result.put("areaResident", row.getCell(6).getStringCellValue());
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setAreaPublic(row.getCell(7).getStringCellValue());

                    result.put("areaPublic", row.getCell(7).getStringCellValue());
                    row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setAreaRself(row.getCell(8).getStringCellValue());

                    result.put("areaRself", row.getCell(8).getStringCellValue());
                    row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setMapx(row.getCell(9).getStringCellValue());

                    result.put("mapx", row.getCell(9).getStringCellValue());
                    row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setMapy(row.getCell(10).getStringCellValue());

                    result.put("mapx", row.getCell(10).getStringCellValue());
                    row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setHeatingCapacityRated(row.getCell(11).getStringCellValue());

                    result.put("heatingCapacityRated", row.getCell(11).getStringCellValue());
                    row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
                    if (row.getCell(12) == null || "".equals(row.getCell(12))) {
                        System.out.println("导入失败第" + j + "行，第12个数据错误");
                        result.put("wd3", "所属行政区为空");
                    }
                    if (city.get(row.getCell(12).getStringCellValue()) == null || "".equals(city.get(row.getCell(12).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第12个数据无对应行政区");
                        result.put("wd3", "无对应行政区");
                    } else {
                        heatsource.setRegionCode(city.get(row.getCell(12).getStringCellValue()).toString());
                    }
                    result.put("regionCode", row.getCell(12).getStringCellValue());

                    heatsource.setEnergyType(row.getCell(13).getStringCellValue());

                    result.put("energyType", row.getCell(13).getStringCellValue());

                    heatsource.setBuildNature(row.getCell(14).getStringCellValue());

                    result.put("buildNature", row.getCell(14).getStringCellValue());
                    row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
                    heatsource.setDutyCalls(row.getCell(15).getStringCellValue());
                    row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
                    result.put("dutyCalls", row.getCell(16).getStringCellValue());
                    //查询是否已有数据 有则更新 无法查询  上边已查  并且计算出的code
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code", heatsource.getCode());
                    BasHeatsource source = heatsourceService.getOne(queryWrapper);
                    if (source != null) {
                        heatsource.setId(source.getId());
                    }
                    list.add(heatsource);
                }
//                heatsourceService.saveOrUpdateBatch(list);
//                System.out.println("=========热源共更新" + list.size() + "数据==========");
            }
        }
    }

    //=============================================================
    // 热源出口 未处理  没有所属热源
    //=============================================================

    public void readExcelFileForHeatSourceExit(InputStream inputStream, String fileName) {
        IBasHeatsourceExitService exitService = SpringContextUtils.getBean(IBasHeatsourceExitService.class);
        getHeatSource();
        List<BasHeatsourceExit> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasHeatsourceExit> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasHeatsourceExit exit = new BasHeatsourceExit();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    exit.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    exit.setHeatsourceExitName(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatSourceMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatSourceMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应热源");
                        return;
                    }
                    exit.setHeatsourceId(heatSourceMap.get(row.getCell(2).getStringCellValue()));
                    exit.setAddress(row.getCell(3).getStringCellValue());
                    exit.setHeatingArea(row.getCell(4).getStringCellValue());
                    exit.setAreaResident(row.getCell(5).getStringCellValue());
                    exit.setAreaPublic(row.getCell(6).getStringCellValue());
                    exit.setAreaRself(row.getCell(7).getStringCellValue());
                    //查询是否已有数据 有则更新
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code", exit.getCode());
                    BasHeatsourceExit basHeatsourceExit = exitService.getOne(queryWrapper);
                    if (basHeatsourceExit != null) {
                        exit.setId(basHeatsourceExit.getId());
                    }
                    list.add(exit);
                }
                exitService.saveOrUpdateBatch(list);
                System.out.println("=========热源出口共更新" + list.size() + "数据==========");
            }
        }


    }

    /**
     * 换热站
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForHeatStation(InputStream inputStream, String fileName) {
        IBasHeatstationService basHeatstationService = SpringContextUtils.getBean(IBasHeatstationService.class);
        getHeatSource();
        getCompany();
        List<BasHeatstation> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasHeatstation> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasHeatstation heatstation = new BasHeatstation();
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(1).getStringCellValue()) == null || "".equals(company.get(row.getCell(1).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第1个数据无对应供热企业");
                        return;
                    }
                    heatstation.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(1).getStringCellValue()).toString()));
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.likeRight("code", companyCodeMap.get(row.getCell(1).getStringCellValue())).orderByDesc("code").last(" limit 1");
                    BasHeatstation basHeatstation = basHeatstationService.getOne(queryWrapper);
                    if (basHeatstation != null) {
                        heatstation.setCode((Long.parseLong(basHeatstation.getCode()) + 1) + "");
                    } else {
                        heatstation.setCode(companyCodeMap.get(row.getCell(1).getStringCellValue()) + "0001");
                    }
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatsourceExitMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatsourceExitMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应热力出口");
                        return;
                    }
                    heatstation.setHeatsourceexitId(heatsourceExitMap.get(row.getCell(2).getStringCellValue()));

                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    heatstation.setHeatstationName(row.getCell(3).getStringCellValue());
                    heatstation.setAddress(row.getCell(4).getStringCellValue());
                    heatstation.setMapx(row.getCell(5).getStringCellValue());
                    heatstation.setMapy(row.getCell(6).getStringCellValue());
                    heatstation.setHeatingArea(row.getCell(7).getStringCellValue());
                    heatstation.setAreaResident(row.getCell(8).getStringCellValue());
                    heatstation.setAreaPublic(row.getCell(9).getStringCellValue());
                    heatstation.setAreaRself(row.getCell(10).getStringCellValue());
                    /*if (row.getCell(11).getStringCellValue() == null || "".equals(row.getCell(11).getStringCellValue())) {
                        System.out.println("导入失败第" + j + "行，第14个数据错误");
                        return;
                    }*/
                    heatstation.setHouseHoldCount(Integer.parseInt(row.getCell(11).getStringCellValue()));
                    /*queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code", heatstation.getCode());
                    BasHeatstation basHeatstation = basHeatstationService.getOne(queryWrapper);
                    if (basHeatstation != null) {
                        heatstation.setId(basHeatstation.getId());
                    }*/
                    list.add(heatstation);
                }
                basHeatstationService.saveOrUpdateBatch(list);
                System.out.println("=========换热站共更新" + list.size() + "数据==========");
            }
        }


    }

    /**
     * 机组
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForCrew(InputStream inputStream, String fileName) {
        IBasCrewService crewService = SpringContextUtils.getBean(IBasCrewService.class);
        getHeatStation();
        getCompany();
        List<BasCrew> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasCrew> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasCrew crew = new BasCrew();
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(1).getStringCellValue()) == null || "".equals(company.get(row.getCell(1).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第1个数据无对应供热企业");
                        return;
                    }
                    crew.setHeatCompanyId(Integer.parseInt(company.get(row.getCell(1).getStringCellValue()).toString()));
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatStationMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatStationMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应换热站");
                        return;
                    }
                    crew.setHeatstationId(heatStationMap.get(row.getCell(2).getStringCellValue()));
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.likeRight("code", heatStationCodeMap.get(row.getCell(2).getStringCellValue())).orderByDesc("code").last("limit 1");
                    BasCrew crew1 = crewService.getOne(queryWrapper);
                    if (crew1 != null) {
                        crew.setCode((Long.parseLong(crew1.getCode()) + 1) + "");
                    } else {
                        crew.setCode(heatStationCodeMap.get(row.getCell(2).getStringCellValue()) + "01");
                    }

                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    crew.setCrewName(row.getCell(3).getStringCellValue());
                    crew.setAreaActual(row.getCell(4).getStringCellValue());
                    crew.setHeatingMode(row.getCell(5).getStringCellValue());
                    crew.setIsUse(row.getCell(6).getStringCellValue().equals("是") ? 1 : 0);
                    crew.setUsedFor(row.getCell(7).getStringCellValue().equals("民建") ? 1 : (row.getCell(7).getStringCellValue().equals("公建") ? 2 : 3));
                    list.add(crew);
                }
                crewService.saveOrUpdateBatch(list);
                System.out.println("=========机组共更新" + list.size() + "数据==========");
            }
        }


    }

    /**
     * 小区
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForCommunity(InputStream inputStream, String fileName) {
        IBasCommunityService communityService = SpringContextUtils.getBean(IBasCommunityService.class);
        getHeatStation();
        getCompany();
        List<BasCommunity> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasCommunity> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasCommunity community = new BasCommunity();
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(1).getStringCellValue()) == null || "".equals(company.get(row.getCell(1).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第1个数据无对应供热企业");
                        return;
                    }
                    community.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(1).getStringCellValue()).toString()));

                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatStationMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatStationMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应换热站");
                        return;
                    }
                    community.setHeatstationId(heatStationMap.get(row.getCell(2).getStringCellValue()));
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.likeRight("code", heatStationCodeMap.get(row.getCell(2).getStringCellValue())).orderByDesc("code").last("limit 1");
                    BasCommunity community1 = communityService.getOne(queryWrapper);
                    if (community1 != null) {
                        community.setCode((Long.parseLong(community1.getCode()) + 1) + "");
                    } else {
                        community1.setCode(heatStationMap.get(row.getCell(2).getStringCellValue()) + "01");
                    }
                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    if (city.get(row.getCell(3).getStringCellValue()) == null || "".equals(city.get(row.getCell(3).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第3个数据无对应行政区");
                        return;
                    }
                    community.setRegionCode(city.get(row.getCell(3).getStringCellValue()).toString());

                    if (row.getCell(4) == null || "".equals(row.getCell(4))) {
                        System.out.println("导入失败第" + j + "行，第4个数据错误");
                        return;
                    }
                    community.setCommunityName(row.getCell(4).getStringCellValue());
                    community.setAddress(row.getCell(5).getStringCellValue());
                    community.setHeatingArea(row.getCell(6).getStringCellValue());
                    community.setMapx(row.getCell(7).getStringCellValue());
                    community.setMapy(row.getCell(8).getStringCellValue());
                    community.setCommunityType(row.getCell(9).getStringCellValue());
                    list.add(community);
                }
                communityService.saveOrUpdateBatch(list);
                System.out.println("=========小区共更新" + list.size() + "数据==========");
            }
        }


    }

    /**
     * 用热建筑
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForBuilding(InputStream inputStream, String fileName) {
        IBasBuildingService basBuildingService = SpringContextUtils.getBean(IBasBuildingService.class);
        getHeatStation();
        getCompany();
        getCommunity();
        getCrew();
        List<BasBuilding> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasBuilding> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasBuilding building = new BasBuilding();

                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(1).getStringCellValue()) == null || "".equals(company.get(row.getCell(1).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第1个数据无对应供热企业");
                        return;
                    }
                    building.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(1).getStringCellValue()).toString()));
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatStationMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatStationMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应换热站");
                        return;
                    }
                    building.setHeatstationId(heatStationMap.get(row.getCell(2).getStringCellValue()));
                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    if (communityMap.get(row.getCell(3).getStringCellValue()) == null || "".equals(communityMap.get(row.getCell(3).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第3个数据无对应小区");
                        return;
                    }
                    building.setCommunityId(communityMap.get(row.getCell(3).getStringCellValue()));
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.likeRight("code", communityCodeMap.get(row.getCell(3).getStringCellValue())).orderByDesc("code").last(" limi 1");
                    BasBuilding building1 = basBuildingService.getOne(queryWrapper);
                    if (building1 != null) {
                        building.setCode((Long.parseLong(building1.getCode()) + 1) + "");
                    } else {
                        building.setCode(communityCodeMap.get(row.getCell(3).getStringCellValue()) + "");
                    }
                    if (row.getCell(4) == null || "".equals(row.getCell(4))) {
                        System.out.println("导入失败第" + j + "行，第4个数据错误");
                        return;
                    }
                    if (crewMap.get(row.getCell(4).getStringCellValue()) == null || "".equals(crewMap.get(row.getCell(4).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第4个数据无对应机组");
                        return;
                    }
                    building.setCrewId(crewMap.get(row.getCell(4).getStringCellValue()));
                    building.setBuildingType(row.getCell(5).getStringCellValue());
                    if (row.getCell(6) == null || "".equals(row.getCell(6))) {
                        System.out.println("导入失败第" + j + "行，第6个数据错误");
                        return;
                    }
                    building.setBuildingName(row.getCell(6).getStringCellValue());
                    building.setAddress(row.getCell(7).getStringCellValue());
                    building.setHeatingArea(row.getCell(8).getStringCellValue());
                    building.setBuildYear(row.getCell(9).getStringCellValue());
                    building.setBuildingStructure(row.getCell(10).getStringCellValue());
                    building.setIsEnergysaving(row.getCell(11).getStringCellValue().equals("是") ? 1 : 0);
                    building.setEnergyConsumptionType(row.getCell(12).getStringCellValue());
                    building.setIsSeparateControl(row.getCell(13).getStringCellValue().equals("是") ? 1 : 0);
                    building.setIsMeasure(row.getCell(14).getStringCellValue().equals("是") ? 1 : 0);
                    building.setIsJlsf(row.getCell(15).getStringCellValue().equals("是") ? "1" : "0");
                    building.setIsSubsidy(row.getCell(16).getStringCellValue().equals("是") ? "1" : "0");
                    building.setIsHeatMeteringTransformed(row.getCell(17).getStringCellValue() == "是" ? "1" : "0");
                    building.setHeatMeteringDate(row.getCell(18).getStringCellValue());
                    building.setIsWallInsulationTransformed(row.getCell(19).getStringCellValue().equals("是") ? "1" : "0");
                    building.setWallInsulationDate(row.getCell(20).getStringCellValue());
                    list.add(building);
                }
                basBuildingService.saveOrUpdateBatch(list);
                System.out.println("=========用热建筑共更新" + list.size() + "数据==========");
            }
        }


    }

    /**
     * 热用户
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForHouseHolder(InputStream inputStream, String fileName) {
        IBasHouseholdService householdService = SpringContextUtils.getBean(IBasHouseholdService.class);
        getCompany();
        getCommunity();
        getCrew();
        getBuilding();
        List<BasHousehold> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasHousehold> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasHousehold household = new BasHousehold();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    household.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    household.setHouseHolder(row.getCell(1).getStringCellValue());
                    household.setUnitNo(row.getCell(2).getStringCellValue());
                    household.setFloor(row.getCell(3).getStringCellValue());
                    household.setHouseNo(row.getCell(4).getStringCellValue());
                    household.setHeatingArea(row.getCell(5).getStringCellValue());

                    if (row.getCell(6) == null || "".equals(row.getCell(6))) {
                        System.out.println("导入失败第" + j + "行，第6个数据错误");
                        return;
                    }
                    if (buildMap.get(row.getCell(6).getStringCellValue()) == null || "".equals(buildMap.get(row.getCell(6).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第6个数据无对应建筑");
                        return;
                    }
                    household.setBuildingId(buildMap.get(row.getCell(6).getStringCellValue()));

                    if (row.getCell(7) == null || "".equals(row.getCell(7))) {
                        System.out.println("导入失败第" + j + "行，第7个数据错误");
                        return;
                    }
                    if (communityMap.get(row.getCell(7).getStringCellValue()) == null || "".equals(communityMap.get(row.getCell(7).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第7个数据无对应小区");
                        return;
                    }
                    household.setCommunityId(communityMap.get(row.getCell(7).getStringCellValue()));
                    if (row.getCell(9) == null || "".equals(row.getCell(9))) {
                        System.out.println("导入失败第" + j + "行，第9个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(9).getStringCellValue()) == null || "".equals(company.get(row.getCell(9).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第9个数据无对应供热企业");
                        return;
                    }
                    household.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(9).getStringCellValue()).toString()));
                    household.setContactTel(row.getCell(12).getStringCellValue());
                    household.setHeatingType(row.getCell(13).getStringCellValue());

                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code", household.getCode());
                    BasHousehold basHousehold = householdService.getOne(queryWrapper);
                    if (basHousehold != null) {
                        household.setId(basHousehold.getId());
                    }
                    list.add(household);
                }
                householdService.saveOrUpdateBatch(list);
                System.out.println("=========热用户共更新" + list.size() + "数据==========");
            }
        }


    }

    //=============================================================
    // 锅炉 未处理  没有所属热源
    //=============================================================


    public void getCompany() {
        IBasHeatcompanyService iBasHeatcompanyService = SpringContextUtils.getBean(IBasHeatcompanyService.class);
        CzAreaService czAreaService = SpringContextUtils.getBean(CzAreaService.class);
        List<BasHeatcompany> list = iBasHeatcompanyService.list();
        for (BasHeatcompany basHeatcompany : list) {
            company.put(basHeatcompany.getHeatCompanyName(), basHeatcompany.getId());
            companyCodeMap.put(basHeatcompany.getHeatCompanyName(), basHeatcompany.getCode());
        }
        List<CzArea> areas = czAreaService.list();
        for (CzArea area : areas) {
            city.put(area.getAreaName(), area.getAreaCode());
        }
    }

    public void getHeatSource() {
        IBasHeatsourceService heatsourceService = SpringContextUtils.getBean(IBasHeatsourceService.class);
        List<BasHeatsource> list = heatsourceService.list();
        for (BasHeatsource heatsource : list) {
            heatSourceMap.put(heatsource.getHeatSourceName(), heatsource.getId());
            heatSourceCodeMap.put(heatsource.getHeatSourceName(), heatsource.getCode());
        }
    }

    public void getHeatStation() {
        IBasHeatstationService service = SpringContextUtils.getBean(IBasHeatstationService.class);
        List<BasHeatstation> list = service.list();
        for (BasHeatstation heatstation : list) {
            heatStationMap.put(heatstation.getHeatstationName(), heatstation.getId());
            heatStationCodeMap.put(heatstation.getHeatstationName(), heatstation.getCode());
        }
    }

    public void getCommunity() {
        IBasCommunityService communityService = SpringContextUtils.getBean(IBasCommunityService.class);
        List<BasCommunity> list = communityService.list();
        for (BasCommunity community : list) {
            communityMap.put(community.getCommunityName(), community.getId());
            communityCodeMap.put(community.getCommunityName(), community.getCode());
        }
    }

    public void getCrew() {
        IBasCrewService crewService = SpringContextUtils.getBean(IBasCrewService.class);
        List<BasCrew> list = crewService.list();
        for (BasCrew crew : list) {
            crewMap.put(crew.getCrewName(), crew.getId());
            crewCodeMap.put(crew.getCrewName(), crew.getCode());
        }
    }

    public void getBuilding() {
        IBasBuildingService basBuildingService = SpringContextUtils.getBean(IBasBuildingService.class);
        List<BasBuilding> list = basBuildingService.list();
        for (BasBuilding building : list) {
            buildMap.put(building.getBuildingName(), building.getId());
            buildCodeMap.put(building.getBuildingName(), building.getCode());
        }
    }
}
